1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Public Class frmStaffPaymentRecord
4
5 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
6 Me.Close()
7 End Sub
8
9 Public Sub GetData()
10 Try
11 con = New SqlConnection(cs)
12 con.Open()
13 cmd = New SqlCommand("select RTRIM(StaffPayment.ID) as [ID],(PaymentID) as [Payment ID],Convert(DateTime,DateFrom,103) as [Date From],Convert(DateTime,dateto,103) as [Date To],RTRIM(Staff.St_ID) as [SID],RTRIM(Staff.StaffID) as [Staff ID],RTRIM(StaffName) as [Staff Name],RTRIM(designation) as [Designation],RTRIM(StaffPayment.salary) as [Salary],RTRIM(presentdays) as [Prsesent Days],RTRIM(advance) as [Advance],RTRIM(deduction) as [Deduction],Convert(DateTime,paymentdate,131) as [Payment Date],RTRIM(modeofpayment) as [Payment Mode],RTRIM(paymentmodedetails) as [Payment Mode Details],RTRIM(netpay) as [Net Pay] from Staffpayment,Staff where Staff.St_ID=StaffPayment.StaffID order by paymentdate", con)
14 Dim myDA As SqlDataAdapter = New SqlDataAdapter(cmd)
15 Dim myDataSet As DataSet = New DataSet()
16 myDA.Fill(myDataSet, "StaffPayment")
17 myDA.Fill(myDataSet, "Staff")
18 dgw.DataSource = myDataSet.Tables("StaffPayment").DefaultView
19 dgw.DataSource = myDataSet.Tables("Staff").DefaultView
20 con.Close()
21 Catch ex As Exception
22 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
23 End Try
24 End Sub
25 Private Sub frmLogs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
26 GetData()
27 End Sub
28 Sub Reset()
29 txtStaffName.Text = ""
30 DateFrom.Text = Today
31 DateTo.Text = Now
32 GetData()
33 End Sub
34 Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
35 Reset()
36 End Sub
37
38
39 Private Sub btnClose_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
40 Me.Close()
41 End Sub
42
43 Private Sub btnExportExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportExcel.Click
44 Dim rowsTotal, colsTotal As Short
45 Dim I, j, iC As Short
46 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
47 Dim xlApp As New Excel.Application
48 Try
49 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
50 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
51 xlApp.Visible = True
52
53 rowsTotal = dgw.RowCount
54 colsTotal = dgw.Columns.Count - 1
55 With excelWorksheet
56 .Cells.Select()
57 .Cells.Delete()
58 For iC = 0 To colsTotal
59 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
60 Next
61 For I = 0 To rowsTotal - 1
62 For j = 0 To colsTotal
63 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
64 Next j
65 Next I
66 .Rows("1:1").Font.FontStyle = "Bold"
67 .Rows("1:1").Font.Size = 12
68
69 .Cells.Columns.AutoFit()
70 .Cells.Select()
71 .Cells.EntireColumn.AutoFit()
72 .Cells(1, 1).Select()
73 End With
74 Catch ex As Exception
75 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
76 Finally
77 'RELEASE ALLOACTED RESOURCES
78 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
79 xlApp = Nothing
80 End Try
81 End Sub
82
83 Private Sub dgw_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
84 Try
85 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
86 If lblSet.Text = "Payment Entry" Then
87 Me.Hide()
88 frmStaffPayment.txtID.Text = dr.Cells(0).Value.ToString()
89 frmStaffPayment.PaymentID.Text = dr.Cells(1).Value.ToString()
90 frmStaffPayment.DateFrom.Text = dr.Cells(2).Value.ToString()
91 frmStaffPayment.DateTo.Text = dr.Cells(3).Value.ToString()
92 frmStaffPayment.txtStID.Text = dr.Cells(4).Value.ToString()
93 frmStaffPayment.StaffID.Text = dr.Cells(5).Value.ToString()
94 frmStaffPayment.StaffName.Text = dr.Cells(6).Value.ToString()
95 frmStaffPayment.Designation.Text = dr.Cells(7).Value.ToString()
96 con = New SqlConnection(cs)
97 con.Open()
98 Dim cp1 As String = "select Salary from Staff where St_id=" & dr.Cells(4).Value & ""
99 cmd = New SqlCommand(cp1)
100 cmd.Connection = con
101 rdr = cmd.ExecuteReader()
102 If rdr.Read() Then
103 frmStaffPayment.txtSalary.Text = rdr.GetValue(0)
104 End If
105 If (rdr IsNot Nothing) Then
106 rdr.Close()
107 End If
108 If con.State = ConnectionState.Open Then
109 con.Close()
110 End If
111 frmStaffPayment.Salary.Text = dr.Cells(8).Value.ToString()
112 frmStaffPayment.PresentDays.Text = dr.Cells(9).Value.ToString()
113 frmStaffPayment.Advance.Text = dr.Cells(10).Value.ToString()
114 frmStaffPayment.Deduction.Text = dr.Cells(11).Value.ToString()
115 frmStaffPayment.PaymentDate.Text = dr.Cells(12).Value.ToString()
116 frmStaffPayment.paymentmode.Text = dr.Cells(13).Value.ToString()
117 frmStaffPayment.PaymentModeDetails.Text = dr.Cells(14).Value.ToString()
118 frmStaffPayment.NetPay.Text = dr.Cells(15).Value.ToString()
119 frmStaffPayment.btnSave.Enabled = False
120 frmStaffPayment.btnDelete.Enabled = True
121 frmStaffPayment.btnUpdate.Enabled = True
122 frmStaffPayment.btnPrint.Enabled = True
123 frmStaffPayment.DateFrom.Enabled = False
124 frmStaffPayment.DateTo.Enabled = False
125 frmStaffPayment.PaymentDate.Enabled = False
126 frmStaffPayment.Deduction.ReadOnly = True
127 frmStaffPayment.dgw.Enabled = False
128 End If
129 Catch ex As Exception
130 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
131 End Try
132
133 End Sub
134
135 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
136 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
137 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
138 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
139 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
140 End If
141 Dim b As Brush = SystemBrushes.ControlText
142 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
143
144 End Sub
145
146 Private Sub txtStaffName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtStaffName.TextChanged
147 Try
148 con = New SqlConnection(cs)
149 con.Open()
150 cmd = New SqlCommand("select RTRIM(StaffPayment.ID) as [ID],(PaymentID) as [Payment ID],Convert(DateTime,DateFrom,103) as [Date From],Convert(DateTime,dateto,103) as [Date To],RTRIM(Staff.St_ID) as [SID],RTRIM(Staff.StaffID) as [Staff ID],RTRIM(StaffName) as [Staff Name],RTRIM(designation) as [Designation],RTRIM(StaffPayment.salary) as [Salary],RTRIM(presentdays) as [Prsesent Days],RTRIM(advance) as [Advance],RTRIM(deduction) as [Deduction],Convert(DateTime,paymentdate,131) as [Payment Date],RTRIM(modeofpayment) as [Payment Mode],RTRIM(paymentmodedetails) as [Payment Mode Details],RTRIM(netpay) as [Net Pay] from Staffpayment,Staff where Staff.St_ID=StaffPayment.StaffID and StaffName like '" & txtStaffName.Text & "%' order by paymentdate", con)
151 Dim myDA As SqlDataAdapter = New SqlDataAdapter(cmd)
152 Dim myDataSet As DataSet = New DataSet()
153 myDA.Fill(myDataSet, "StaffPayment")
154 myDA.Fill(myDataSet, "Staff")
155 dgw.DataSource = myDataSet.Tables("StaffPayment").DefaultView
156 dgw.DataSource = myDataSet.Tables("Staff").DefaultView
157 con.Close()
158 Catch ex As Exception
159 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
160 End Try
161 End Sub
162
163 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
164 Try
165 con = New SqlConnection(cs)
166 con.Open()
167 cmd = New SqlCommand("select RTRIM(StaffPayment.ID) as [ID],(PaymentID) as [Payment ID],Convert(DateTime,DateFrom,103) as [Date From],Convert(DateTime,dateto,103) as [Date To],RTRIM(Staff.St_ID) as [SID],RTRIM(Staff.StaffID) as [Staff ID],RTRIM(StaffName) as [Staff Name],RTRIM(designation) as [Designation],RTRIM(StaffPayment.salary) as [Salary],RTRIM(presentdays) as [Prsesent Days],RTRIM(advance) as [Advance],RTRIM(deduction) as [Deduction],Convert(DateTime,paymentdate,131) as [Payment Date],RTRIM(modeofpayment) as [Payment Mode],RTRIM(paymentmodedetails) as [Payment Mode Details],RTRIM(netpay) as [Net Pay] from Staffpayment,Staff where Staff.St_ID=StaffPayment.StaffID and PaymentDate Between @d1 and @d2 order by paymentdate", con)
168 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "DateIN").Value = DateFrom.Value.Date
169 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "DateIN").Value = DateTo.Value
170 Dim myDA As SqlDataAdapter = New SqlDataAdapter(cmd)
171 Dim myDataSet As DataSet = New DataSet()
172 myDA.Fill(myDataSet, "StaffPayment")
173 myDA.Fill(myDataSet, "Staff")
174 dgw.DataSource = myDataSet.Tables("StaffPayment").DefaultView
175 dgw.DataSource = myDataSet.Tables("Staff").DefaultView
176 con.Close()
177 Catch ex As Exception
178 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
179 End Try
180 End Sub
181 End Class
